package com.cwvs.dao;

import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import javax.annotation.Resource;

import org.apache.commons.collections.MapUtils;
import org.apache.commons.lang.StringUtils;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import com.cwvs.helper.UrlHelper;
import com.cwvs.utils.CommonUtils;
import com.cwvs.utils.MySqlPageUtil;
import com.cwvs.utils.Pages;

/**
 * 
 * 店铺相关DAO
 */
@Repository
public class AppStoreDao {
	@Resource
	private JdbcTemplate jdbcTemplate;
	@Resource
	private MySqlPageUtil mySqlPageUtil;
	@Resource
	private UrlHelper urlHelper;

	/**
	 * 店铺列表
	 * 
	 * @return
	 */
	public List<Map<String, Object>> storeList(String proviceId, String cityId,
			String countyId, String streetId, String type, String outType) {
		StringBuffer sbf = new StringBuffer();
		sbf.append("SELECT ID,CODE,NAME,PIC_URL,PRICE FROM APP_STORE WHERE 1=1 ");
		List<Object> params = new ArrayList<Object>();
		if (StringUtils.isNotBlank(proviceId)) {
			sbf.append(" AND PROVINCE_ID=? ");
			params.add(proviceId);
		}
		if (StringUtils.isNotBlank(cityId)) {
			sbf.append(" AND CITY_ID=? ");
			params.add(cityId);
		}
		if (StringUtils.isNotBlank(countyId)) {
			sbf.append(" AND COUNTY_ID=? ");
			params.add(countyId);
		}
		if (StringUtils.isNotBlank(streetId)) {
			sbf.append(" AND STREET_ID=? ");
			params.add(countyId);
		}
		if (StringUtils.isNotBlank(type)) {
			sbf.append(" AND STORE_TYPE=? ");
			params.add(Integer.parseInt(type));
		}
		if (StringUtils.isNotBlank(outType)) {
			sbf.append(" AND OUT_TYPE=? ");
			params.add(outType);
		}
		return jdbcTemplate.queryForList(sbf.toString(), params.toArray());
	}

	/**
	 * 店铺列表
	 * 
	 * @return
	 */
	public Pages storeList(Map<String, Object> m, Pages page) {
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT T1.ID,T1.CODE,T1.NAME,T1.STORE_TYPE ");
		sql.append(",T1.PROVINCE_ID,T1.CITY_ID,T1.COUNTY_ID,T1.STREET_ID ");
		sql.append(" ,T2.NAME PROVINCE_NAME,T3.NAME CITY_NAME ");
		sql.append(" ,T4.NAME COUNTY_NAME,T5.NAME STREET_NAME,T1.PRICE ");
		sql.append(" ,DATE_FORMAT(T1.CTIME,'%Y-%m-%d %T') C_TIME ");
		sql.append(" ,DATE_FORMAT(T1.UTIME,'%Y-%m-%d %T') U_TIME ");
		sql.append(" ,T1.ADDRESS,T1.DESCRIPTION,T1.PIC_URL FROM APP_STORE T1  ");
		sql.append(" LEFT JOIN APP_AREA T2 ON T2.ID=T1.PROVINCE_ID ");
		sql.append(" LEFT JOIN APP_AREA T3 ON T3.ID=T1.CITY_ID ");
		sql.append(" LEFT JOIN APP_AREA T4 ON T4.ID=T1.COUNTY_ID ");
		sql.append(" LEFT JOIN APP_AREA T5 ON T5.ID=T1.STREET_ID ");
		sql.append(" WHERE 1=1  ");
		List<Object> params = new ArrayList<Object>();
		if (StringUtils.isNotBlank(MapUtils.getString(m, "PROVINCE_ID"))) {
			sql.append(" AND T1.PROVINCE_ID=?  ");
			params.add(MapUtils.getString(m, "PROVINCE_ID"));
		}
		if (StringUtils.isNotBlank(MapUtils.getString(m, "CITY_ID"))) {
			sql.append(" AND T1.CITY_ID=?  ");
			params.add(MapUtils.getString(m, "CITY_ID"));
		}
		if (StringUtils.isNotBlank(MapUtils.getString(m, "COUNTY_ID"))) {
			sql.append(" AND T1.COUNTY_ID=?  ");
			params.add(MapUtils.getString(m, "COUNTY_ID"));
		}
		if (StringUtils.isNotBlank(MapUtils.getString(m, "STREET_ID"))) {
			sql.append(" AND T1.STREET_ID=?  ");
			params.add(MapUtils.getString(m, "STREET_ID"));
		}
		if (StringUtils.isNotBlank(MapUtils.getString(m, "STORE_TYPE"))) {
			sql.append(" AND T1.STORE_TYPE=?  ");
			params.add(MapUtils.getIntValue(m, "STORE_TYPE"));
		}
		if (StringUtils.isNotBlank(MapUtils.getString(m, "NAME"))) {
			sql.append(" AND UPPER(T1.NAME) LIKE ? ");
			params.add("%" + MapUtils.getString(m, "NAME").toUpperCase() + "%");
		}
		if (StringUtils.isNotBlank(MapUtils.getString(m, "CODE"))) {
			sql.append(" AND UPPER(T1.CODE) LIKE ?  ");
			params.add("%" + MapUtils.getString(m, "CODE").toUpperCase() + "%");
		}
		sql.append(" ORDER BY T1.UTIME DESC ");
		String page_sql = mySqlPageUtil.getPageSql(page, sql.toString());
		List<Map<String, Object>> ls = jdbcTemplate.queryForList(page_sql,
				params.toArray());
		for (Map<String, Object> map : ls) {
			map.put("PIC_URL", urlHelper.img01UrlRootWithPort(MapUtils
					.getString(map, "PIC_URL")));
		}
		page.setPage(ls);
		page.setTotalRecord(storeCount(m));
		return page;
	}

	private int storeCount(Map<String, Object> m) {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT COUNT(1) FROM APP_STORE T1 WHERE 1=1 ");
		List<Object> params = new ArrayList<Object>();
		if (StringUtils.isNotBlank(MapUtils.getString(m, "PROVINCE_ID"))) {
			sql.append(" AND T1.PROVINCE_ID=?  ");
			params.add(MapUtils.getString(m, "PROVINCE_ID"));
		}
		if (StringUtils.isNotBlank(MapUtils.getString(m, "CITY_ID"))) {
			sql.append(" AND T1.CITY_ID=?  ");
			params.add(MapUtils.getString(m, "CITY_ID"));
		}
		if (StringUtils.isNotBlank(MapUtils.getString(m, "COUNTY_ID"))) {
			sql.append(" AND T1.COUNTY_ID=?  ");
			params.add(MapUtils.getString(m, "COUNTY_ID"));
		}
		if (StringUtils.isNotBlank(MapUtils.getString(m, "STREET_ID"))) {
			sql.append(" AND T1.STREET_ID=?  ");
			params.add(MapUtils.getString(m, "STREET_ID"));
		}
		if (StringUtils.isNotBlank(MapUtils.getString(m, "STORE_TYPE"))) {
			sql.append(" AND T1.STORE_TYPE=?  ");
			params.add(MapUtils.getIntValue(m, "STORE_TYPE"));
		}
		if (StringUtils.isNotBlank(MapUtils.getString(m, "NAME"))) {
			sql.append(" AND UPPER(T1.NAME) LIKE ? ");
			params.add("%" + MapUtils.getString(m, "NAME").toUpperCase() + "%");
		}
		if (StringUtils.isNotBlank(MapUtils.getString(m, "CODE"))) {
			sql.append(" AND UPPER(T1.CODE) LIKE ?  ");
			params.add("%" + MapUtils.getString(m, "CODE").toUpperCase() + "%");
		}
		return jdbcTemplate.queryForInt(sql.toString(), params.toArray());
	}

	/**
	 * 通过ID获取店铺信息
	 * 
	 * @param id
	 * @return
	 */
	public Map<String, Object> getStoreInfoById(String id) {
		if (StringUtils.isBlank(id)) {
			return new HashMap<String, Object>();
		}
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT T1.ID,T1.CODE,T1.NAME,T1.STORE_TYPE,T1.ADDRESS ");
		sql.append(",T1.PROVINCE_ID,T1.CITY_ID,T1.COUNTY_ID,T1.STREET_ID ");
		sql.append(" ,T2.NAME PROVINCE_NAME,T3.NAME CITY_NAME,T1.DESCRIPTION ");
		sql.append(" ,T4.NAME COUNTY_NAME,T5.NAME STREET_NAME,T1.PRICE ");
		sql.append(" ,DATE_FORMAT(T1.CTIME,'%Y-%m-%d %T') C_TIME ");
		sql.append(" ,DATE_FORMAT(T1.UTIME,'%Y-%m-%d %T') U_TIME ");
		sql.append(" ,T1.PIC_URL FROM APP_STORE T1  ");
		sql.append(" LEFT JOIN APP_AREA T2 ON T2.ID=T1.PROVINCE_ID ");
		sql.append(" LEFT JOIN APP_AREA T3 ON T3.ID=T1.CITY_ID ");
		sql.append(" LEFT JOIN APP_AREA T4 ON T4.ID=T1.COUNTY_ID ");
		sql.append(" LEFT JOIN APP_AREA T5 ON T5.ID=T1.STREET_ID ");
		sql.append(" WHERE T1.id=?  ");
		List<Map<String, Object>> ls = jdbcTemplate.queryForList(
				sql.toString(), new Object[] { id });
		if (!ls.isEmpty()) {
			Map<String, Object> map = ls.get(0);
			map.put("PIC_URL", urlHelper.img01UrlRootWithPort(MapUtils
					.getString(map, "PIC_URL")));
			map.put("TYPE_NAME", CommonUtils.getStoreTypeName(MapUtils
					.getString(map, "STORE_TYPE")));
			return map;
		}
		return new HashMap<String, Object>();
	}

	/**
	 * 保存或者修改店铺医院
	 */
	public boolean saveOrUpdate(Map<String, Object> o) {
		if (StringUtils.isBlank(MapUtils.getString(o, "ID"))) {
			StringBuffer sbf = new StringBuffer();
			String id = UUID.randomUUID().toString();
			sbf.append(" INSERT INTO APP_STORE(");
			sbf.append("ID,PIC_URL,CODE,NAME,STORE_TYPE ");
			sbf.append(",OUT_TYPE,PROVINCE_ID,CITY_ID,COUNTY_ID ");
			sbf.append(",STREET_ID,PRICE,ADDRESS,DESCRIPTION,CTIME,UTIME)VALUES(");
			sbf.append("?,?,?,?,?,?,?,?,?,?,?,?,?,now(),now())");
			List<Object> ls = new ArrayList<Object>();
			ls.add(id);
			ls.add(MapUtils.getString(o, "PIC_URL"));
			ls.add(MapUtils.getString(o, "CODE"));
			ls.add(MapUtils.getString(o, "NAME"));
			ls.add(MapUtils.getIntValue(o, "STORE_TYPE"));
			ls.add(MapUtils.getString(o, "OUT_TYPE"));
			ls.add(MapUtils.getString(o, "PROVINCE_ID"));
			ls.add(MapUtils.getString(o, "CITY_ID"));
			ls.add(MapUtils.getString(o, "COUNTY_ID"));
			ls.add(MapUtils.getString(o, "STREET_ID"));
			ls.add(MapUtils.getDouble(o, "PRICE", 0.00));
			ls.add(MapUtils.getString(o, "ADDRESS"));
			ls.add(MapUtils.getString(o, "DESCRIPTION"));
			return jdbcTemplate.update(sbf.toString(), ls.toArray()) > 0;
		} else {
			StringBuffer sbf = new StringBuffer();
			sbf.append(" UPDATE APP_STORE SET PIC_URL=?, CODE=?,NAME=? ");
			sbf.append(" STORE_TYPE=? ,OUT_TYPE=? PROVINCE_ID=?,UTIME=now(), ");
			sbf.append(" CITY_ID=? ,COUNTY_ID=?,STREET_ID=? ,PRICE=? ");
			sbf.append(" WHERE ID=?  ");
			List<Object> ls = new ArrayList<Object>();
			ls.add(MapUtils.getString(o, "PIC_URL"));
			ls.add(MapUtils.getString(o, "CODE"));
			ls.add(MapUtils.getString(o, "NAME"));
			ls.add(MapUtils.getIntValue(o, "STORE_TYPE"));
			ls.add(MapUtils.getString(o, "OUT_TYPE"));
			ls.add(MapUtils.getString(o, "PROVINCE_ID"));
			ls.add(MapUtils.getString(o, "CITY_ID"));
			ls.add(MapUtils.getString(o, "COUNTY_ID"));
			ls.add(MapUtils.getString(o, "STREET_ID"));
			ls.add(MapUtils.getDouble(o, "PRICE", 0.00));
			ls.add(MapUtils.getString(o, "ADDRESS"));
			ls.add(MapUtils.getString(o, "DESCRIPTION"));
			ls.add(MapUtils.getString(o, "ID"));
			return jdbcTemplate.update(sbf.toString(), ls.toArray()) > 0;
		}

	}

	/**
	 * 获取外卖类型列表
	 */
	public List<Map<String, Object>> outTypeList() {
		return jdbcTemplate
				.queryForList("SELECT ID,CODE,NAME,REMARK FROM APP_TAKE_OUT_TYPE WHERE IS_ACTIVE=1 ");
	}

	/**
	 * 科室信息保存
	 */
	public boolean saveStoreDepartment(Map<String, Object> m) {
		StringBuffer sbf = new StringBuffer();
		sbf.append(" insert into app_store_department (id,store_id,name,ctime)");
		sbf.append("values(?,?,?,?)");
		return jdbcTemplate.update(
				sbf.toString(),
				new Object[] { UUID.randomUUID().toString(),
						MapUtils.getString(m, "STORE_ID"),
						MapUtils.getString(m, "NAME"), new Date() }) > 0;
	}
}
